package com.scalar.forecast.forecastEntry.dao;

import com.scalar.forecast.forecastEntry.RowData;
import com.scalar.forecast.forecastEntry.SlotData;
import com.scalar.forecast.forecastEntry.TableColumn;
import com.scalar.forecast.forecastEntry.TableRowBean;
import com.scalar.forecast.forecastEntry.forms.ForecastEntryForm;
import com.scalar.forecast.utils.ForecastSqlQueryMappings;
import com.scalar.forecast.forecastSetup.dao.ForecastSetupDAO;
import com.scalar.forecast.forecastSetup.ForecastEntryField;
import com.scalar.forecast.forecastSetup.ForecastTimeFrame;
import com.scalar.forecast.commons.Constants;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.ScalarException;

import java.util.*;
import java.sql.*;
import java.sql.Date;

/**
 * User: Sujan Kumar
 * Date: Feb 15, 2005
 * Time: 7:35:12 PM
 */
public class ForecastEntryDAO
{

    public static RowData getSalesPersonAsRowData (DBTransaction dbTransaction, int salesPersonId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        RowData rowData = new RowData();
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
               conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.getSalesPerson" ) );
            pstmt.setInt ( 1, salesPersonId );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                int levelOrder = rset.getInt( "LEVELORDER" );
                String salesPersonName = rset.getString ( "SalesPerson_Name" );
                rowData.setMaxLevelOrder( levelOrder );
                rowData.setLevelOrder( levelOrder );
                rowData.setSalesPersonId( salesPersonId );
                rowData.setSalesPersonName( salesPersonName );
            }
            else
            {
                System.out.println ( "---Sales Person does't exist with id:" + salesPersonId );
                throw new ScalarException ( "SalesPerson Does't Exist" );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exception while getting the salesPerson details--" );
            throw new ScalarException ( "Could Not get SalesPerson" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) { }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return rowData;
    }
    public static LinkedList getChildRowDataList (DBTransaction dbTransaction, int salesPersonId) throws ScalarException
    {
        LinkedList childRowDataList = new LinkedList();
        RowData childRowData = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.getSalesPersonWithManagerId" ) );
            pstmt.setInt ( 1, salesPersonId );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                childRowData = new RowData();
                childRowData.setLevelOrder( rset.getInt ( "LEVELORDER" ) );
                childRowData.setSalesPersonId( rset.getInt ( "SPERSONID" ) );
                childRowData.setSalesPersonName( rset.getString ( "SalesPerson_Name" ) );
                childRowDataList.add( childRowData );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exception while getting the childRowData List--" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not get Child Row Data List" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) { }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return childRowDataList;
    }

    /**
     * This method returns the slotData corresponding to the salesPersonID and the slot number.
     * @param dbTransaction
     * @param slotNo
     * slotNo starts from 0.
     * @param forecastEntryForm
     * this form is used to get the custId,projId, prodId... values to use in getting the slot data.
     * @param salesPersonId
     * Id for the salesPerson.
     * @return
     * @throws ScalarException
     */
    public static SlotData getSlotData (DBTransaction dbTransaction, int slotNo, ForecastEntryForm forecastEntryForm, int salesPersonId) throws ScalarException
    {
        SlotData slotData = new SlotData();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        try
        {
            String QUERY = buildQuery(dbTransaction, slotNo, forecastEntryForm, salesPersonId);
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( QUERY );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                slotData.setRowId( rset.getInt ( "RowId" ) );
                slotData.setQty( rset.getInt ( "Qty" ) );
                slotData.setPrice( rset.getDouble ( "Price" ) );
                slotData.setConfidenceLevel( rset.getFloat ( "Confidence_level" ) );
                slotData.setUserList1( rset.getString ( "UserList1" ) );
                slotData.setUserDate1( StringUtils.formatDateToString( rset.getDate ( "UserDate1"), "" ) );
                slotData.setUserTxt1( rset.getString ( "UserTxt1" ) );
                slotData.setUserTxt2( rset.getString ( "UserTxt2" ) );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exceptio while loading the slot Data---" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not get Slot Data" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) { }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return slotData;
    }
    private static String buildQuery(DBTransaction dbTransaction, int slotNo, ForecastEntryForm forecastEntryForm, int salesPersonId) throws ScalarException
    {
        StringBuffer QUERY = new StringBuffer ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.select.forecastSalesEntry" ) );
        QUERY.append ( " WHERE SPersonId = " );
        QUERY.append ( salesPersonId );
        QUERY.append ( " " );
        ArrayList setupFieldList = ForecastSetupDAO.getForecastSetupFields(dbTransaction);
        ForecastEntryField setupField = null;
        int setupFieldListSize = setupFieldList.size();
        for ( int i=0; i < setupFieldListSize; i++ )
        {
            setupField = ( ForecastEntryField )setupFieldList.get ( i );
            if ( "By Customer".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                if ( forecastEntryForm.getCustId() <= 0 )
                {
                    QUERY.append ( " AND CustId IS NULL " );
                }
                else
                {
                    QUERY.append ( " AND CustId = " );
                    QUERY.append ( forecastEntryForm.getCustId() );
                }
            }
            else if ( "By Project".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                if ( forecastEntryForm.getProjectId() <= 0 )
                {
                    QUERY.append ( " AND ProjId IS NULL" );
                }
                else
                {
                    QUERY.append ( " AND ProjId = " );
                    QUERY.append ( forecastEntryForm.getProjectId() );
                }
            }
            else if ( "By Product".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                QUERY.append ( " AND ProdId = " );
                QUERY.append ( forecastEntryForm.getProductId() );
            }
            else if ( "By ODM".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                if ( forecastEntryForm.getOdmId() <= 0 )
                {
                    QUERY.append ( " AND OdmId IS NULL " );
                }
                else
                {
                    QUERY.append ( " AND OdmId = " );
                    QUERY.append ( forecastEntryForm.getOdmId() );
                }
            }
        }

        ForecastTimeFrame timeframe = ForecastSetupDAO.getForecastTimeFrame(dbTransaction);
        if ( "Month".equalsIgnoreCase( timeframe.getTfName() ) )
        {
            int yearCnt = 0;
            yearCnt = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) / 12;
            int month = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) % 12;
            int year = forecastEntryForm.getYear() + yearCnt;

            QUERY.append ( " AND Month = " );
            QUERY.append ( month );
            QUERY.append ( " AND Year = " );
            QUERY.append ( year );
        }
        else if ( "Quarter".equalsIgnoreCase( timeframe.getTfName() ) )
        {
            int yearCnt = 0;
            yearCnt = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) / 4;
            int quarter = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) % 4;
            int year = forecastEntryForm.getYear() + yearCnt;

            QUERY.append ( " AND Quarter = " );
            QUERY.append ( quarter );
            QUERY.append ( " AND Year = " );
            QUERY.append ( year );
        }
        else if ( "Year".equalsIgnoreCase( ( timeframe.getTfName() ) ) )
        {
            int year = forecastEntryForm.getStartMonth_Quarter_Year() + ( slotNo );
            QUERY.append ( " AND Year = " );
            QUERY.append ( year );
        }
        return QUERY.toString();
    }

    public static TableColumn getEditColumn (DBTransaction dbTransaction, String tableName, String entryName, String userId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        TableColumn tableColumn = null;
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.loadEditColumn" ) );
            pstmt.setString ( 1, tableName );
            pstmt.setString ( 2, entryName );
            pstmt.setString ( 3, userId );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                tableColumn = new TableColumn ();

                tableColumn.setName( rset.getString ( "COLNAME" ) );
                tableColumn.setDisplayName( rset.getString ( "COLDISPLAY" ) );
                tableColumn.setSortOrder ( rset.getInt ( "SORTORDER" ) );

            }
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception While getting the edit column details----" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not get Edit Column Details" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) { }
            }
            if ( rset != null )
            {
                try{ rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return tableColumn;
    }

    public static String getUserDefinedEntryNameColumn (DBTransaction dbTransaction, String entryName) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String column = null;
        boolean success  = false;
        if ( entryName == null )
        {
            return null;
        }
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.loadUserFields" ) );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                if ( entryName.equalsIgnoreCase( rset.getString ( "UserList1" ) ) )
                {
                    column = Constants.COLUMN_USERLIST1;
                }
                else if ( entryName.equalsIgnoreCase( rset.getString ( "UserDate1" ) ) )
                {
                    column = Constants.COLUMN_USERDATE1;
                }
                else if ( entryName.equalsIgnoreCase( rset.getString ( "UserTxt1" ) ) )
                {
                    column = Constants.COLUMN_USERTXT1;
                }
                else if ( entryName.equalsIgnoreCase( rset.getString ( "UserTxt2" ) ) )
                {
                    column = Constants.COLUMN_USERTXT2;
                }
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exception While getting the column ----" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not get Column Name" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) { }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return column;
    }
    public static boolean saveSalesForecastEntry (DBTransaction dbTransaction, TableRowBean tableRowBean) throws ScalarException
    {
        boolean success = false;
        Connection conn = null;
        CallableStatement cstmt = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            cstmt = conn.prepareCall ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.saveSalesForecast" ) );
            cstmt.setInt ( 1, tableRowBean.getRowId() );
            cstmt.setInt ( 2, tableRowBean.getSalesPersonId() );
            cstmt.setInt ( 3, tableRowBean.getProductId() );
            cstmt.setString ( 4, ( tableRowBean.getCustId() <= 0 ) ? null : Integer.toString( tableRowBean.getCustId() ) );
            cstmt.setString ( 5,  ( tableRowBean.getOdmId() <= 0 ) ? null : Integer.toString ( tableRowBean.getOdmId() ) );
            cstmt.setString ( 6, ( tableRowBean.getProjId() <= 0 ) ? null : Integer.toString ( tableRowBean.getProjId() ) );
            cstmt.setString ( 7, ( tableRowBean.getMonth() < 0 ) ? null : Integer.toString ( tableRowBean.getMonth() ) );
            cstmt.setString ( 8, ( tableRowBean.getQuarter() < 0 ) ? null : Integer.toString ( tableRowBean.getQuarter() ) );
            cstmt.setString ( 9, ( tableRowBean.getYear() < 0 ) ? null : Integer.toString ( tableRowBean.getYear() ) );
            cstmt.setInt ( 10, tableRowBean.getQty() );
            cstmt.setString ( 11, ( tableRowBean.getPrice() < 0 ) ? null : Double.toString ( tableRowBean.getPrice() ) );
            cstmt.setString ( 12, ( tableRowBean.getConfidenceLevel() < 0 ) ? null : Float.toString( tableRowBean.getConfidenceLevel() ) );
            cstmt.setString ( 13, tableRowBean.getUserList1() );
            cstmt.setDate ( 14, StringUtils.getSQLDate( tableRowBean.getUserDate1() ) );
            cstmt.setString ( 15, tableRowBean.getUserTxt1() );
            cstmt.setString( 16, tableRowBean.getUserTxt2() );
            cstmt.setString( 17, tableRowBean.getUserId() );
            cstmt.registerOutParameter( 18, java.sql.Types.INTEGER );

            cstmt.execute();
            success = ( cstmt.getInt ( 18 ) == 0 ) ? true : false;
        }
        catch ( Exception e )
        {
            System.out.println ( "------Exception while saving sales forecast Entry-----" );
            e.printStackTrace();
            throw new ScalarException( "Could Not Save Sales Forecast" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException e ) { }
                }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return success;
    }

    /**
     * This method is used to get the salespersonId for the given user...
     * @param dbTransaction
     * @param userId
     * login id for the user
     * @return
     * returns salesPersonId<br>
     * returns null if the user is not salesPerson.
     *
     */
    public static String getSalesPersonIdForUser ( DBTransaction dbTransaction, String userId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        String salesPersonId = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.loadSalesPersonId" ) );
            pstmt.setString ( 1, userId );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                salesPersonId = rset.getString ( "SPERSONID" );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exception while getting the salesPersonId--" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not Get SalesPersonId For User" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ) { }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return salesPersonId;
    }

    public static ArrayList getMaxLevelSalesPersonIds ( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        int salesPersonId = -1;
        ArrayList salesPersonIds = new ArrayList();
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.select.maxLevel.salesPersonId" ) );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                salesPersonId = rset.getInt ( "SPERSONID" );
                salesPersonIds.add ( new Integer ( salesPersonId ) );
            }
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exception while getting the max level salesPersonID--" );
            e.printStackTrace();
            throw new ScalarException( "Could Not get Max Level Sales Person Ids--" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return salesPersonIds;
    }
    public static boolean saveBuildForecastEntry (DBTransaction dbTransaction, TableRowBean tableRowBean, String buildNo ) throws ScalarException
    {
        boolean success = false;
        Connection conn = null;
        CallableStatement cstmt = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            cstmt = conn.prepareCall ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.saveBuildForecast" ) );
            cstmt.setString ( 1, buildNo );
            cstmt.setString ( 2, ( tableRowBean.getSalesPersonId() <= 0 ) ? null : Integer.toString ( tableRowBean.getSalesPersonId() ) );
            cstmt.setInt ( 3, tableRowBean.getProductId() );
            cstmt.setString ( 4, ( tableRowBean.getCustId() <= 0 ) ? null : Integer.toString( tableRowBean.getCustId() ) );
            cstmt.setString ( 5, ( tableRowBean.getOdmId() <= 0 ) ? null : Integer.toString( tableRowBean.getOdmId() ) );
            cstmt.setString ( 6, ( tableRowBean.getProjId() <= 0 ) ? null : Integer.toString( tableRowBean.getProjId() ) );
            cstmt.setString ( 7, ( tableRowBean.getMonth() < 0 ) ? null : Integer.toString( tableRowBean.getMonth() ) );
            cstmt.setString ( 8, ( tableRowBean.getQuarter() < 0 ) ? null : Integer.toString( tableRowBean.getQuarter() ) );
            cstmt.setString ( 9, ( tableRowBean.getYear() < 0 ) ? null : Integer.toString( tableRowBean.getYear() ) );
            cstmt.setInt ( 10, tableRowBean.getQty() );
            cstmt.setString ( 11, ( tableRowBean.getPrice() < 0 ) ? null : Double.toString ( tableRowBean.getPrice() ) );
            cstmt.setString ( 12, tableRowBean.getUserList1() );
            cstmt.setDate ( 13, StringUtils.getSQLDate( tableRowBean.getUserDate1() ) );
            cstmt.setString ( 14, tableRowBean.getUserTxt1() );
            cstmt.setString( 15, tableRowBean.getUserTxt2() );
            cstmt.setString( 16, tableRowBean.getUserId() );
            cstmt.registerOutParameter( 17, java.sql.Types.INTEGER );

            cstmt.execute();
            success = ( cstmt.getInt ( 17 ) == 0 ) ? true : false;
        }
        catch ( Exception e )
        {
            System.out.println ( "------Exception while saving sales forecast Entry-----" );
            e.printStackTrace();
            throw new ScalarException( "Could Not Save Build Forecast" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException e ) { }
                }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return success;
    }

    public static int getBuildMasterRowId ( DBTransaction dbTransaction, int slot, ForecastEntryForm forecastEntryForm ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        int rowId = -1;
        String QUERY = getBuildRowIdQuery( dbTransaction, slot, forecastEntryForm );
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( QUERY );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                rowId = rset.getInt ( "RowId" );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exception While getting the Build Master RowId--" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not Get the Build Maste RowId" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ) { }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return rowId;
    }
    public static int getMonth ( int slot, int startMonth, String timeFrame )
    {
        int month = -1;
        if ( Constants.FORECAST_TIMEFRAME_MONTH.equalsIgnoreCase( timeFrame ) )
        {
            month = ( slot + startMonth ) % 12;
        }
        return month;
    }
    public static int getQuarter ( int slot, int startQuarter, String timeFrame )
    {
        int quarter = -1;
        if ( Constants.FORECAST_TIMEFRAME_QUARTER.equalsIgnoreCase( timeFrame ) )
        {
            quarter = ( slot + startQuarter ) % 4;
        }
        return quarter;
    }
    public static int getYear ( int slot, int start, int year, String timeFrame )
    {
        int resultYear = -1;
        if ( Constants.FORECAST_TIMEFRAME_MONTH.equalsIgnoreCase( timeFrame ) )
        {
            int startMonth = start;
            int yearCnt = ( slot + startMonth ) / 12;
            resultYear = year + yearCnt;
        }
        if ( Constants.FORECAST_TIMEFRAME_QUARTER.equalsIgnoreCase( timeFrame ) )
        {
            int startQuarter = start;
            int yearCnt = ( slot + startQuarter ) / 4;
            resultYear = year + yearCnt;
        }
        if ( Constants.FORECAST_TIMEFRAME_YEAR.equalsIgnoreCase( timeFrame ) )
        {
            int startYear = start;
            resultYear = startYear + slot;
        }
        return resultYear;
    }
    public static String getBuildRowIdQuery ( DBTransaction dbTransaction, int slot, ForecastEntryForm forecastEntryForm ) throws ScalarException
    {
        StringBuffer QUERY = new StringBuffer ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.select.forecastBuildEntry" ) );
        QUERY.append ( " " );
        ArrayList setupFieldList = ForecastSetupDAO.getForecastSetupFields(dbTransaction);
        ForecastEntryField setupField = null;
        int setupFieldListSize = setupFieldList.size();
        boolean isWhereAdded = false;
        for ( int i=0; i < setupFieldListSize; i++ )
        {
            setupField = ( ForecastEntryField )setupFieldList.get ( i );
            if ( "By Customer".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                if ( isWhereAdded )
                {
                    QUERY.append ( " AND " );
                }
                else
                {
                    QUERY.append ( " WHERE ");
                    isWhereAdded = true;
                }
                if ( forecastEntryForm.getCustId() <= 0 )
                {
                    QUERY.append ( " CustId IS NULL " );
                }
                else
                {
                    QUERY.append ( " CustId = " );
                    QUERY.append ( forecastEntryForm.getCustId() );
                }
            }
            else if ( "By Project".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                if ( isWhereAdded )
                {
                    QUERY.append ( " AND " );
                }
                else
                {
                    QUERY.append ( " WHERE ");
                    isWhereAdded = true;
                }
                if ( forecastEntryForm.getProjectId() <= 0 )
                {
                    QUERY.append ( " ProjId IS NULL " );
                }
                else
                {
                    QUERY.append ( " ProjId = " );
                    QUERY.append ( forecastEntryForm.getProjectId() );
                }
            }
            else if ( "By Product".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                if ( isWhereAdded )
                {
                    QUERY.append ( " AND " );
                }
                else
                {
                    QUERY.append ( " WHERE ");
                    isWhereAdded = true;
                }
                if ( forecastEntryForm.getProductId() <= 0 )
                {
                    QUERY.append ( " ProdId IS NULL " );
                }
                else
                {
                    QUERY.append ( " ProdId = " );
                    QUERY.append ( forecastEntryForm.getProductId() );
                }
            }
            else if ( "By ODM".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                if ( isWhereAdded )
                {
                    QUERY.append ( " AND " );
                }
                else
                {
                    QUERY.append ( " WHERE ");
                    isWhereAdded = true;
                }
                if ( forecastEntryForm.getOdmId() <= 0 )
                {
                   QUERY.append ( " OdmId IS NULL " );
                }
                else
                {
                    QUERY.append ( " OdmId = " );
                    QUERY.append ( forecastEntryForm.getOdmId() );
                }
            }
        }

        ForecastTimeFrame timeframe = ForecastSetupDAO.getForecastTimeFrame(dbTransaction);
        if ( "Month".equalsIgnoreCase( timeframe.getTfName() ) )
        {
            if ( isWhereAdded )
            {
                QUERY.append ( " AND " );
            }
            else
            {
                QUERY.append ( " WHERE ");
                isWhereAdded = true;
            }
            /*int yearCnt = 0;
            yearCnt = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) / 12;
            int month = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) % 12;
            int year = forecastEntryForm.getYear() + yearCnt;*/

            QUERY.append ( " Month = " );
            QUERY.append ( getMonth( slot, forecastEntryForm.getStartMonth_Quarter_Year(), timeframe.getTfName() ) );
            QUERY.append ( " AND Year = " );
            QUERY.append ( getYear( slot, forecastEntryForm.getStartMonth_Quarter_Year(), forecastEntryForm.getYear(), timeframe.getTfName() ) );
        }
        else if ( "Quarter".equalsIgnoreCase( timeframe.getTfName() ) )
        {
            if ( isWhereAdded )
            {
                QUERY.append ( " AND " );
            }
            else
            {
                QUERY.append ( " WHERE ");
                isWhereAdded = true;
            }
            /*int yearCnt = 0;
            yearCnt = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) / 4;
            int quarter = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) % 4;
            int year = forecastEntryForm.getYear() + yearCnt;*/

            QUERY.append ( " Quarter = " );
            QUERY.append ( getQuarter( slot, forecastEntryForm.getStartMonth_Quarter_Year(), timeframe.getTfName() ) );
            QUERY.append ( " AND Year = " );
            QUERY.append ( getYear( slot, forecastEntryForm.getStartMonth_Quarter_Year(), forecastEntryForm.getYear(), timeframe.getTfName() ) );
        }
        else if ( "Year".equalsIgnoreCase( ( timeframe.getTfName() ) ) )
        {
            if ( isWhereAdded )
            {
                QUERY.append ( " AND " );
            }
            else
            {
                QUERY.append ( " WHERE ");
                isWhereAdded = true;
            }
            /*int year = forecastEntryForm.getStartMonth_Quarter_Year() + ( slotNo );*/
            QUERY.append ( " Year = " );
            QUERY.append ( getYear( slot, forecastEntryForm.getStartMonth_Quarter_Year(), forecastEntryForm.getYear(), timeframe.getTfName() ) );
        }
        return QUERY.toString();
    }
    public static boolean isForecastEntryAllowed ( int start, int year ) throws ScalarException
    {
        boolean isAllowed = false;
        ForecastTimeFrame timeFrame = ForecastSetupDAO.getForecastTimeFrame(null);
        long freezingPoint = 0;
        long daysElapsed = 0;
        try
        {
            freezingPoint = Long.parseLong ( timeFrame.getTfCompany() );
        }
        catch ( NumberFormatException e ){ }
        Calendar now = Calendar.getInstance();
        Calendar forecastStart = Calendar.getInstance();
        if ( Constants.FORECAST_TIMEFRAME_MONTH.equalsIgnoreCase( timeFrame.getTfName() ) )
        {
            int month = start;
            if ( Constants.MEASURE_TYPE_DAYS.equalsIgnoreCase( timeFrame.getTfMeasureType() ) )
            {
                forecastStart.set ( Calendar.MONTH, month );
                forecastStart.set ( Calendar.YEAR, year );
                forecastStart.set ( Calendar.DATE, 1 );
            }
            else if ( Constants.MEASURE_TYPE_WEEKS.equalsIgnoreCase( timeFrame.getTfMeasureType() ) )
            {
                freezingPoint = freezingPoint * 7;
                forecastStart.set ( Calendar.MONTH, month );
                forecastStart.set ( Calendar.YEAR, year );
                forecastStart.set ( Calendar.DATE, 1 );
            }
            else if ( Constants.MEASURE_TYPE_MONTHS.equalsIgnoreCase( timeFrame.getTfMeasureType() ) )
            {
                int currentMonth = now.get ( Calendar.MONTH );
                int currentYear = now.get ( Calendar.YEAR );
                if ( year < currentYear )
                {
                    return false;
                }
                else if ( month < currentMonth && year == currentYear )
                {
                    return false;
                }
                return true;
            }
        }
        if ( Constants.FORECAST_TIMEFRAME_QUARTER.equalsIgnoreCase( timeFrame.getTfName() ) )
        {
            int quarterStartMths [] = { 0, 3, 6, 9 };
            int quarterMth = quarterStartMths[ start ];
            if ( Constants.MEASURE_TYPE_DAYS.equalsIgnoreCase( timeFrame.getTfMeasureType() ) )
            {
                forecastStart.set ( Calendar.MONTH, quarterMth );
                forecastStart.set ( Calendar.YEAR, year );
                forecastStart.set ( Calendar.DATE, 1 );
            }
            else if ( Constants.MEASURE_TYPE_WEEKS.equalsIgnoreCase( timeFrame.getTfMeasureType() ) )
            {
                freezingPoint = freezingPoint * 7;
                forecastStart.set ( Calendar.MONTH, quarterMth );
                forecastStart.set ( Calendar.YEAR, year );
                forecastStart.set ( Calendar.DATE, 1 );
            }
            else if ( Constants.MEASURE_TYPE_MONTHS.equalsIgnoreCase( timeFrame.getTfMeasureType() ) )
            {
                int monthQuarters[] = { 0,0,0, 1,1,1, 2,2,2, 3,3,3 };
                int currentMonth = now.get ( Calendar.MONTH );
                int currentQtr = monthQuarters[currentMonth];
                int startQtr = start;
                int currentYear = now.get ( Calendar.YEAR );
                int monthsElapsedInCurrentQtr = ( currentMonth % 3 ) + 1;
                if ( year > currentYear )
                {
                    return true;
                }
                if ( year < currentYear )
                {
                    return false;
                }
                else if ( startQtr < currentQtr && year == currentYear )
                {
                    return false;
                }
                else if ( startQtr > currentQtr && year == currentYear )
                {
                    return true;
                }
                else if ( freezingPoint < monthsElapsedInCurrentQtr && year == currentYear )
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }

        }
        if ( Constants.FORECAST_TIMEFRAME_YEAR.equalsIgnoreCase( timeFrame.getTfName() ) )
        {
            int startYear = start;
            if ( Constants.MEASURE_TYPE_DAYS.equalsIgnoreCase( timeFrame.getTfMeasureType() ) )
            {
                forecastStart.set ( Calendar.MONTH, 0 );
                forecastStart.set ( Calendar.YEAR, startYear );
                forecastStart.set ( Calendar.DATE, 1 );
            }
            else if ( Constants.MEASURE_TYPE_WEEKS.equalsIgnoreCase( timeFrame.getTfMeasureType() ) )
            {
                freezingPoint = freezingPoint * 7;
                forecastStart.set ( Calendar.MONTH, 0 );
                forecastStart.set ( Calendar.YEAR, startYear );
                forecastStart.set ( Calendar.DATE, 1 );
            }
            else if ( Constants.MEASURE_TYPE_MONTHS.equalsIgnoreCase( timeFrame.getTfMeasureType() ) )
            {
                int currentMonth = now.get ( Calendar.MONTH );
                int currentYear = now.get ( Calendar.YEAR );
                if ( startYear < currentYear )
                {
                    return false;
                }
                else if ( freezingPoint < ( currentMonth+1 )  && startYear == currentYear )
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        daysElapsed = ( ( now.getTimeInMillis() - forecastStart.getTimeInMillis() ) / ( 1000 * 60 * 60 * 24 ) ) + 1;

        if ( daysElapsed >= 0 && ( daysElapsed > freezingPoint ) )
        {
            isAllowed = false;
        }
        else
        {
            isAllowed = true;
        }
        return isAllowed;
    }
    public static String getNextBuildNo( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        StringBuffer buildNo = new StringBuffer ( Constants.FORECAST_BUILD_NO_PREFIX );
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.getBuildNoCount" ) );
            rset = pstmt.executeQuery();
            if ( rset.next () )
            {
                int cnt = rset.getInt ( 1 );
                cnt++;
                buildNo.append( cnt );
            }
            else
            {
                System.out.println ( "---Could Not get next build number--" );
                throw new ScalarException ( "Could Not Get Next Build Number--" );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception while getting Next Build Number--" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not Get Next Build Number--" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return buildNo.toString();
    }
    public static LinkedList getBuildNoRowDataList (DBTransaction dbTransaction, String buildNo ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        RowData rowData = new RowData();
        LinkedList rowDataList = new LinkedList( );
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
               conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.getBuildNoSalesPersonInfo" ) );

            pstmt.setString ( 1, buildNo );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                rowData = new RowData();
                int levelOrder = rset.getInt( "LEVELORDER" );
                int salesPersonId = rset.getInt ( "SPERSONID");
                String salesPersonName = rset.getString ( "SalesPerson_Name" );
                rowData.setMaxLevelOrder( levelOrder );
                rowData.setLevelOrder( levelOrder );
                rowData.setSalesPersonId( salesPersonId );
                rowData.setSalesPersonName( salesPersonName );
                rowDataList.add ( rowData );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exception while getting the Build salesPerson details--" );
            throw new ScalarException ( "Could Not get SalesPerson" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) { }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return rowDataList;
    }

    public static SlotData getBuilNoSlotData (DBTransaction dbTransaction,int slotNo, ForecastEntryForm forecastEntryForm, int salesPersonId, String buildNo ) throws ScalarException
    {
        SlotData slotData = new SlotData();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        try
        {
            String QUERY = buildQueryForBuildNo(dbTransaction, slotNo, forecastEntryForm, salesPersonId, buildNo);

            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( QUERY );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                slotData.setRowId( rset.getInt ( "RowId" ) );
                slotData.setQty( rset.getInt ( "Qty" ) );
                slotData.setPrice( rset.getDouble ( "Price" ) );
                //slotData.setConfidenceLevel( rset.getFloat ( "Confidence_level" ) );
                slotData.setUserList1( rset.getString ( "UserList1" ) );
                slotData.setUserDate1( StringUtils.formatDateToString( rset.getDate ( "UserDate1"), "" ) );
                slotData.setUserTxt1( rset.getString ( "UserTxt1" ) );
                slotData.setUserTxt2( rset.getString ( "UserTxt2" ) );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exception while loading the Build slot Data---" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not get Build Slot Data" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) { }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return slotData;
    }
    private static String buildQueryForBuildNo ( DBTransaction dbTransaction, int slotNo, ForecastEntryForm forecastEntryForm, int salesPersonId, String buildNo ) throws ScalarException
    {
        StringBuffer QUERY = new StringBuffer ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.select.forecastBuildEntryForBuildNo" ) );
        if ( salesPersonId <= 0 )
        {
           QUERY.append ( " WHERE SPersonId IS NULL " );
        }
        else
        {
            QUERY.append ( " WHERE SPersonId = " );
            QUERY.append ( salesPersonId );
        }
        QUERY.append ( " " );
        QUERY.append ( " AND BuildNo = " );
        QUERY.append ( "'" );
        QUERY.append ( buildNo );
        QUERY.append ( "' " );
        ArrayList setupFieldList = ForecastSetupDAO.getForecastSetupFields(dbTransaction);
        ForecastEntryField setupField = null;
        int setupFieldListSize = setupFieldList.size();
        for ( int i=0; i < setupFieldListSize; i++ )
        {
            setupField = ( ForecastEntryField )setupFieldList.get ( i );
            if ( "By Customer".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                if ( forecastEntryForm.getCustId() <= 0 )
                {
                    QUERY.append ( " AND CustId IS NULL " );
                }
                else
                {
                    QUERY.append ( " AND CustId = " );
                    QUERY.append ( forecastEntryForm.getCustId() );
                }
            }
            else if ( "By Project".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                if ( forecastEntryForm.getProjectId() <= 0 )
                {
                    QUERY.append ( " AND ProjId IS NULL" );
                }
                else
                {
                    QUERY.append ( " AND ProjId = " );
                    QUERY.append ( forecastEntryForm.getProjectId() );
                }
            }
            else if ( "By Product".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                QUERY.append ( " AND ProdId = " );
                QUERY.append ( forecastEntryForm.getProductId() );
            }
            else if ( "By ODM".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                if ( forecastEntryForm.getOdmId() <= 0 )
                {
                    QUERY.append ( " AND OdmId IS NULL " );
                }
                else
                {
                    QUERY.append ( " AND OdmId = " );
                    QUERY.append ( forecastEntryForm.getOdmId() );
                }
            }
        }

        ForecastTimeFrame timeframe = ForecastSetupDAO.getForecastTimeFrame(dbTransaction);
        if ( "Month".equalsIgnoreCase( timeframe.getTfName() ) )
        {
            int yearCnt = 0;
            yearCnt = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) / 12;
            int month = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) % 12;
            int year = forecastEntryForm.getYear() + yearCnt;

            QUERY.append ( " AND Month = " );
            QUERY.append ( month );
            QUERY.append ( " AND Year = " );
            QUERY.append ( year );
        }
        else if ( "Quarter".equalsIgnoreCase( timeframe.getTfName() ) )
        {
            int yearCnt = 0;
            yearCnt = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) / 4;
            int quarter = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) % 4;
            int year = forecastEntryForm.getYear() + yearCnt;

            QUERY.append ( " AND Quarter = " );
            QUERY.append ( quarter );
            QUERY.append ( " AND Year = " );
            QUERY.append ( year );
        }
        else if ( "Year".equalsIgnoreCase( ( timeframe.getTfName() ) ) )
        {
            int year = forecastEntryForm.getStartMonth_Quarter_Year() + ( slotNo );
            QUERY.append ( " AND Year = " );
            QUERY.append ( year );
        }
        return QUERY.toString();
    }

    /**
     * This method is used to validate the salesPerson LevelOrder.
     * @param dbTransaction
     * @param salesPersonId
     * @return
     * @throws ScalarException
     */
    public static boolean isSlesPersonLevelValid( DBTransaction dbTransaction, String salesPersonId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        boolean isValid = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.isSalesPersonLevelValid" ) );
            pstmt.setString ( 1, salesPersonId );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                int cnt = 0;
                cnt = rset.getInt ( 1 );
                if ( cnt > 0 )
                {
                    isValid = true;
                }
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception While Validating SalesPerson Level Order " );
            e.printStackTrace();
            throw new ScalarException ( "Could Not Validate SalesPerson LevelOrder" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) { }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return isValid;
    }

    /**
     * This method returns the slotData corresponding to the salesPersonID and the slot number.
     * @param dbTransaction
     * @param slotNo
     * slotNo starts from 0.
     * @param forecastEntryForm
     * this form is used to get the custId,projId, prodId... values to use in getting the slot data.
     * @param salesPersonId
     * Id for the salesPerson.
     * @return
     * @throws ScalarException
     */
    public static SlotData getActualSlotData (DBTransaction dbTransaction, int slotNo, ForecastEntryForm forecastEntryForm,int salesPersonId ) throws ScalarException
    {
        SlotData slotData = new SlotData();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        try
        {
            String searchCriteria = buildActualSlotDataSearchCriteria( dbTransaction, slotNo, forecastEntryForm, salesPersonId );
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.select.salesPerson.actual.salesEntry" ) + searchCriteria + " ORDER BY ENTRYDATE DESC" );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                slotData.setRowId( rset.getInt ( "RowId" ) );
                slotData.setQty( rset.getInt ( "Qty" ) );
                slotData.setPrice( rset.getDouble ( "Price" ) );
                slotData.setConfidenceLevel( rset.getFloat ( "Confidence_level" ) );
                slotData.setUserList1( rset.getString ( "UserList1" ) );
                slotData.setUserDate1( StringUtils.formatDateToString( rset.getDate ( "UserDate1"), "" ) );
                slotData.setUserTxt1( rset.getString ( "UserTxt1" ) );
                slotData.setUserTxt2( rset.getString ( "UserTxt2" ) );
            }
			else
			{
				try { rset.close(); } catch ( SQLException ignore ) { }
				if ( pstmt != null )
				{
					try { pstmt.close(); } catch ( SQLException ignore ) { }
				}
				pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.select.salesPerson.actual.salesHistoryEntry" ) + searchCriteria + " ORDER BY ENTRYDATE DESC" );
				rset = pstmt.executeQuery();
				if ( rset.next() )
				{
					slotData.setRowId( rset.getInt ( "RowId" ) );
					slotData.setQty( rset.getInt ( "Qty" ) );
					slotData.setPrice( rset.getDouble ( "Price" ) );
					slotData.setConfidenceLevel( rset.getFloat ( "Confidence_level" ) );
					slotData.setUserList1( rset.getString ( "UserList1" ) );
					slotData.setUserDate1( StringUtils.formatDateToString( rset.getDate ( "UserDate1"), "" ) );
					slotData.setUserTxt1( rset.getString ( "UserTxt1" ) );
					slotData.setUserTxt2( rset.getString ( "UserTxt2" ) );
				}
			}
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exceptio while loading Actual slot Data---" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not get Actual Slot Data" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) { }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return slotData;
    }

    private static String buildActualSlotDataSearchCriteria ( DBTransaction dbTransaction, int slotNo, ForecastEntryForm forecastEntryForm, int salesPersonId ) throws ScalarException
    {

        StringBuffer searchCriteria = new StringBuffer();
        searchCriteria.append ( " WHERE SPersonId = " );
        searchCriteria.append ( salesPersonId );
        searchCriteria.append ( " " );
        searchCriteria.append ( " AND UserId = " );
        searchCriteria.append ( " ( SELECT TOP 1 USERID FROM FCSPERSONREL WHERE SPERSONID = " + salesPersonId +" )" );

        ArrayList setupFieldList = ForecastSetupDAO.getForecastSetupFields(dbTransaction);
        ForecastEntryField setupField = null;
        int setupFieldListSize = setupFieldList.size();
        for ( int i=0; i < setupFieldListSize; i++ )
        {
            setupField = ( ForecastEntryField )setupFieldList.get ( i );
            if ( "By Customer".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                if ( forecastEntryForm.getCustId() <= 0 )
                {
                    searchCriteria.append ( " AND CustId IS NULL " );
                }
                else
                {
                    searchCriteria.append ( " AND CustId = " );
                    searchCriteria.append ( forecastEntryForm.getCustId() );
                }
            }
            else if ( "By Project".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                if ( forecastEntryForm.getProjectId() <= 0 )
                {
                    searchCriteria.append ( " AND ProjId IS NULL" );
                }
                else
                {
                    searchCriteria.append ( " AND ProjId = " );
                    searchCriteria.append ( forecastEntryForm.getProjectId() );
                }
            }
            else if ( "By Product".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                searchCriteria.append ( " AND ProdId = " );
                searchCriteria.append ( forecastEntryForm.getProductId() );
            }
            else if ( "By ODM".equalsIgnoreCase( setupField.getEntryName() ) && setupField.isEntrySelected() )
            {
                if ( forecastEntryForm.getOdmId() <= 0 )
                {
                    searchCriteria.append ( " AND OdmId IS NULL " );
                }
                else
                {
                    searchCriteria.append ( " AND OdmId = " );
                    searchCriteria.append ( forecastEntryForm.getOdmId() );
                }
            }
        }

        ForecastTimeFrame timeframe = ForecastSetupDAO.getForecastTimeFrame(dbTransaction);
        if ( "Month".equalsIgnoreCase( timeframe.getTfName() ) )
        {
            int yearCnt = 0;
            yearCnt = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) / 12;
            int month = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) % 12;
            int year = forecastEntryForm.getYear() + yearCnt;

            searchCriteria.append ( " AND Month = " );
            searchCriteria.append ( month );
            searchCriteria.append ( " AND Year = " );
            searchCriteria.append ( year );
        }
        else if ( "Quarter".equalsIgnoreCase( timeframe.getTfName() ) )
        {
            int yearCnt = 0;
            yearCnt = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) / 4;
            int quarter = ( slotNo + forecastEntryForm.getStartMonth_Quarter_Year() ) % 4;
            int year = forecastEntryForm.getYear() + yearCnt;

            searchCriteria.append ( " AND Quarter = " );
            searchCriteria.append ( quarter );
            searchCriteria.append ( " AND Year = " );
            searchCriteria.append ( year );
        }
        else if ( "Year".equalsIgnoreCase( ( timeframe.getTfName() ) ) )
        {
            int year = forecastEntryForm.getStartMonth_Quarter_Year() + ( slotNo );
            searchCriteria.append ( " AND Year = " );
            searchCriteria.append ( year );
        }

        /*
            QUERY FORMAT: SELECT * FROM FCENTRYMASTER <searchCriteria> AND EntryDate = ( SELECT MAX ( EntryDate) FROM FCENTRYMASTER <searchCriteria> )
        */
        /*StringBuffer QUERY = new StringBuffer ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.select.salesPerson.actual.salesEntry" ) );
        QUERY.append ( " " );
        QUERY.append( searchCriteria.toString( ) );
        QUERY.append ( " AND EntryDate = ( " );
        QUERY.append( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.select.salesPerson.actual.salesEntry.maxEntryDate" ) );
        QUERY.append ( " " );
        QUERY.append ( searchCriteria.toString( ) );
        QUERY.append ( " )");*/
        return searchCriteria.toString();
    }
    public static RowData getSalesBacklog ( DBTransaction dbTransaction, ForecastEntryForm forecastEntryForm, int dateCategory,int backlogType ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        RowData rowData = new RowData();
        boolean success = false;
        int showNext = forecastEntryForm.getShowNext();
        int start = forecastEntryForm.getStartMonth_Quarter_Year();
        int startYear = forecastEntryForm.getYear();
        ForecastTimeFrame forecastTimeFrame = ForecastSetupDAO.getForecastTimeFrame( dbTransaction );
        String tfName = forecastTimeFrame.getTfName();
        LinkedList slotDataList = new LinkedList();
        SlotData slotData = null;
        for ( int i = 0; i < showNext; i++ )
        {
            slotDataList.add( new SlotData() );
        }
        rowData.setSlotDataList( slotDataList );
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.load.sales.backlog" ) );
            pstmt.setInt ( 1, forecastEntryForm.getCustId() );
            pstmt.setInt ( 2, forecastEntryForm.getProductId() );
            pstmt.setInt ( 3, dateCategory );
            if ( Constants.FORECAST_TIMEFRAME_MONTH.equalsIgnoreCase( tfName ) )
            {
                pstmt.setInt ( 4, start );
                pstmt.setInt ( 5, showNext );
                pstmt.setInt ( 6, startYear );
            }
            else if ( Constants.FORECAST_TIMEFRAME_QUARTER.equalsIgnoreCase( tfName ) )
            {
                int quarterStart[] = { 0, 3, 6, 9 };
                pstmt.setInt ( 4, quarterStart [start] );
                pstmt.setInt ( 5, showNext*3 );
                pstmt.setInt ( 6, startYear );
            }
            else if ( Constants.FORECAST_TIMEFRAME_YEAR.equalsIgnoreCase( tfName ) )
            {
                pstmt.setInt ( 4, 0 );
                pstmt.setInt ( 5, showNext*12 );
                pstmt.setInt ( 6, start );
            }
            pstmt.setString( 7, StringUtils.formatDateToString( new Date( System.currentTimeMillis() ) ) );
            pstmt.setInt( 8, backlogType );

            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                int month = rset.getInt ( "MONTH" );
                int year = rset.getInt ( "YEAR" );
                int slotIndex = getSlotIndex( start, startYear, month, year, tfName );

                if ( slotIndex >=0 && slotIndex < showNext )
                {
                    slotData = ( SlotData ) slotDataList.get ( slotIndex );
					int qty = rset.getInt ( "QUANTITY" );
					double price = rset.getDouble( "UNIT_PRICE" );
                    slotData.setQty( slotData.getQty()+ qty );
                    slotData.setPrice( slotData.getPrice() + price );
                }
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception while getting the backlog information---" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not Get Backlog Information" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) { }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return rowData;
    }
    private static int getSlotIndex ( int start, int startYear, int month, int year, String tfName )
    {
        int yearGap = year - startYear;
        int slotIndex = -1;
        if ( Constants.FORECAST_TIMEFRAME_MONTH.equalsIgnoreCase( tfName ) )
        {
            int totalMonths = month + ( yearGap*12 );
            slotIndex = totalMonths - start;
        }
        else if ( Constants.FORECAST_TIMEFRAME_QUARTER.equalsIgnoreCase( tfName ) )
        {
            int monthsToQuarter [] = { 0,0,0, 1,1,1, 2,2,2, 3,3,3 };
            int totalQuarters = monthsToQuarter[month] + ( yearGap * 4 );
            slotIndex = totalQuarters - start;
        }
        else if ( Constants.FORECAST_TIMEFRAME_YEAR.equalsIgnoreCase( tfName ) )
        {
            slotIndex = year - start;
        }

        return slotIndex;
    }
    public static RowData getShippedOrders ( DBTransaction dbTransaction, ForecastEntryForm forecastEntryForm ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        RowData rowData = new RowData();
        int start = forecastEntryForm.getStartMonth_Quarter_Year();
        int startYear = forecastEntryForm.getYear();
        int showNext = forecastEntryForm.getShowNext();
        ForecastTimeFrame forecastTimeFrame = ForecastSetupDAO.getForecastTimeFrame( dbTransaction );
        String tfName = forecastTimeFrame.getTfName();
        boolean success = false;
        LinkedList slotDataList = new LinkedList();
        SlotData slotData = null;
        for ( int i = 0; i < showNext; i++ )
        {
            slotDataList.add( new SlotData() );
        }
        rowData.setSlotDataList( slotDataList );
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.load.shipped.oreders" ) );
            pstmt.setInt ( 1, forecastEntryForm.getCustId() );
            pstmt.setInt ( 2, forecastEntryForm.getProductId() );
            if ( Constants.FORECAST_TIMEFRAME_MONTH.equalsIgnoreCase( tfName ) )
            {
                pstmt.setInt ( 3, start );
                pstmt.setInt ( 4, showNext );
                pstmt.setInt ( 5, startYear );
            }
            else if ( Constants.FORECAST_TIMEFRAME_QUARTER.equalsIgnoreCase( tfName ) )
            {
                int quarterStart[] = { 0, 3, 6, 9 };
                pstmt.setInt ( 3, quarterStart [start] );
                pstmt.setInt ( 4, showNext*3 );
                pstmt.setInt ( 5, startYear );
            }
            else if ( Constants.FORECAST_TIMEFRAME_YEAR.equalsIgnoreCase( tfName ) )
            {
                pstmt.setInt ( 3, 0 );
                pstmt.setInt ( 4, showNext*12 );
                pstmt.setInt ( 5, start );
            }
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                int month = rset.getInt ( "MONTH" );
                int year = rset.getInt ( "YEAR" );
                int slotIndex = getSlotIndex( start, startYear, month, year, tfName );
                if ( slotIndex >=0 && slotIndex < showNext )
                {
                    slotData = ( SlotData ) slotDataList.get ( slotIndex );
                    slotData.setQty( slotData.getQty()+ rset.getInt ( "QUANTITY" ));
                    slotData.setPrice( slotData.getPrice() +rset.getDouble( "UNIT_PRICE" ) );
                }
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception while getting the shipped orders information---" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not Get Shipped Orders Information" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) { }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return rowData;
    }
}
